CREATE PROCEDURE [dbo].[asi_WriteGLTransactionLines]
@transKey uniqueidentifier,
@sequenceNumber int,
@glEntryType nvarchar(50)
AS
    DECLARE @glTransactionLineKey uniqueidentifier
    DECLARE @glTransactionKey uniqueidentifier
    DECLARE @amount decimal (18,4)
    DECLARE @glAccountKey uniqueidentifier
    DECLARE @description nvarchar(50)
    DECLARE @paymentKey uniqueidentifier
    DECLARE @productKey uniqueidentifier
    DECLARE @deferralTermsKey uniqueidentifier
    DECLARE @targetGLAccountKey uniqueidentifier
    DECLARE @deferralProcessedFlag bit
    DECLARE @invoiceLineKey uniqueidentifier
    DECLARE @transactionSequenceNumber int
    DECLARE @lineGroup int
    DECLARE @realSequenceNumber int
    DECLARE @lineGroupNumber int
    DECLARE @groupCounter int
    SET @groupCounter = 0
    SET @realSequenceNumber = -1
    SET @transactionSequenceNumber = 0
    
    -- The UniqueLineGroups table variable keeps track of the unique combination of GLAccounts and
    -- PaymentKeys used in the GLTransactionLines.
    -- We need to set different LineGroup values for different combinations.
    DECLARE @UniqueLineGroups TABLE
        (GLAccountKey uniqueidentifier,
         PaymentKey uniqueidentifier,
         GroupNumber int)

    DECLARE Get_TransactionLines cursor for
    SELECT  newid(), @transKey, sum(#tmpTransLine.Amount),
            #tmpTransLine.AccountKey, #tmpTransLine.Description, #tmpTransLine.PaymentKey,
            #tmpTransLine.ProductKey, #tmpTransLine.DeferralTermsKey,
            #tmpTransLine.TargetGLAccountKey, 0, #tmpTransLine.InvoiceLineKey,
            MIN(#tmpTransLine.TransactionSequenceNumber)
        FROM #tmpTransLine
        INNER JOIN #tmpTransaction
            ON (#tmpTransLine.InvoiceKey = #tmpTransaction.InvoiceKey
               OR #tmpTransLine.PaymentKey = #tmpTransaction.PaymentKey)
            AND #tmpTransLine.FinEntityKey = #tmpTransaction.FinEntityKey
        WHERE #tmpTransaction.TransactionKey = @transKey
            AND #tmpTransLine.GLEntryType = @glEntryType        
        GROUP BY
            #tmpTransLine.AccountKey, #tmpTransLine.Description, #tmpTransLine.PaymentKey,
            #tmpTransLine.ProductKey, #tmpTransLine.DeferralTermsKey,
            #tmpTransLine.TargetGLAccountKey, #tmpTransLine.InvoiceLineKey,
            #tmpTransLine.GLEntryType,
            #tmpTransLine.InvoiceLineNumber
        ORDER BY #tmpTransLine.InvoiceLineNumber
        

    OPEN Get_TransactionLines
    FETCH NEXT FROM Get_TransactionLines into @glTransactionLineKey, @glTransactionKey, @amount,
        @glAccountKey, @description, @paymentKey, @productKey , @deferralTermsKey, @targetGLAccountKey,
        @deferralProcessedFlag, @invoiceLineKey,  @transactionSequenceNumber
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@glEntryType = 'UnearnedIncome'
          OR @glEntryType = 'AccountsReceivable')
        BEGIN
            SET @lineGroupNumber = null
            SELECT @lineGroupNumber = GroupNumber from @UniqueLineGroups
                WHERE GLAccountKey = @glAccountKey
                    AND PaymentKey = ISNULL(@paymentKey, '00000000-0000-0000-0000-000000000000')
            IF @lineGroupNumber IS NULL
            BEGIN
                -- found a new account
                INSERT @UniqueLineGroups(GLAccountKey, GroupNumber, PaymentKey)
                    VALUES(@glAccountKey, @groupCounter, ISNULL(@paymentKey, '00000000-0000-0000-0000-000000000000'))
                SET @lineGroupNumber = @groupCounter
                SET @groupCounter = @groupCounter + 1
            END    
            IF (@realSequenceNumber < 0)
                SET @realSequenceNumber = @transactionSequenceNumber + @sequenceNumber
            ELSE
                SET  @realSequenceNumber =  @realSequenceNumber + 1
        
            INSERT GLTransactionLine(GLTransactionLineKey, GLTransactionKey, Amount, GLAccountKey,
            Description, PaymentKey, ProductKey, DeferralTermsKey,
            TargetGLAccountKey, DeferralProcessedFlag, InvoiceLineKey, GLEntryType,
            TransactionSequenceNumber, LineGroup)
            VALUES (@glTransactionLineKey, @glTransactionKey, @amount, @glAccountKey,
                @description, @paymentKey, @productKey , @deferralTermsKey, @targetGLAccountKey,
                @deferralProcessedFlag, @invoiceLineKey, @glEntryType, @realSequenceNumber,
                @lineGroupNumber)
        
        END  
        
        ELSE
        BEGIN
            INSERT GLTransactionLine(GLTransactionLineKey, GLTransactionKey, Amount, GLAccountKey,
            Description, PaymentKey, ProductKey, DeferralTermsKey,
            TargetGLAccountKey, DeferralProcessedFlag, InvoiceLineKey, GLEntryType,
            TransactionSequenceNumber, LineGroup)
            VALUES (@glTransactionLineKey, @glTransactionKey, @amount, @glAccountKey,
                @description, @paymentKey, @productKey , @deferralTermsKey, @targetGLAccountKey,
                @deferralProcessedFlag, @invoiceLineKey, @glEntryType, @transactionSequenceNumber + @sequenceNumber,
                @transactionSequenceNumber)
        
        END
    nextTransactionLine:
    FETCH NEXT FROM Get_TransactionLines into @glTransactionLineKey, @glTransactionKey, @amount,
        @glAccountKey, @description, @paymentKey, @productKey , @deferralTermsKey, @targetGLAccountKey,
        @deferralProcessedFlag, @invoiceLineKey,  @transactionSequenceNumber
    END
    CLOSE Get_TransactionLines
    DEALLOCATE Get_TransactionLines
    DELETE @UniqueLineGroups
        
    SET @sequenceNumber = @sequenceNumber + @transactionSequenceNumber
    RETURN @sequenceNumber

GO